/*
****************************************************************************************************************************
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('CrossTab') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	PRINT 'Droping procedure dbo.CrossTab'
	DROP PROCEDURE dbo.CrossTab
END
GO

PRINT 'Creating procedure dbo.CrossTab'
GO

CREATE PROCEDURE dbo.CrossTab
(
  @table sysname,       	-- Table to crosstab
  @citery nvarchar(1024),       -- Table to crosstab critery 
  @onrows nvarchar(128),  	-- Grouping key values (on rows)
  @onrowsalias sysname = NULL, 	-- Alias for grouping column
  @oncols nvarchar(128),  	-- Destination columns (on columns)
  @sumcol sysname = NULL  	-- Data cells
)
AS
BEGIN
	DECLARE
	  @sql AS varchar(8000),
	  @NEWLINE AS char(1)

	SET @NEWLINE = CHAR(10)
	SET @sql = 'SELECT' + @NEWLINE + ' ' + @onrows +
	  CASE
		WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
		ELSE ''
	  END

	CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

	DECLARE @keyssql AS varchar(1000)
	SET @keyssql = 
	  'INSERT INTO #keys ' +
	  'SELECT DISTINCT CAST([' + @oncols + '] AS nvarchar(100))' +
	  'FROM ' + @table + ' WHERE '+@citery

	EXEC (@keyssql)

	DECLARE @key AS nvarchar(100)
	SELECT @key = MIN(keyvalue) FROM #keys

	WHILE @key IS NOT NULL
	BEGIN
	  SET @sql = @sql + ','                   + @NEWLINE +
		'  SUM(CASE CAST(' + @oncols +
						 ' AS nvarchar(100))' + @NEWLINE +
		'        WHEN N''' + @key +
			   ''' THEN ' + CASE
							  WHEN @sumcol IS NULL THEN '1'
							  ELSE @sumcol
							END + @NEWLINE +
		'        ELSE 0'                      + @NEWLINE +
		'      END) AS [' + @key+']'
	  
	  SELECT @key = MIN(keyvalue) FROM #keys
	  WHERE keyvalue > @key
	END
	SET @sql = @sql         + @NEWLINE +
	  'FROM ' + @table      + @NEWLINE +
	  'WHERE ' + @citery + @NEWLINE +
	  'GROUP BY ' + @onrows + @NEWLINE +
	  'ORDER BY ' + @onrows

	--PRINT @sql  + @NEWLINE -- For debug
	EXEC (@sql)
END
GO